Tema¶
Dota 2 é um jogo eletrônico do gênero Multiplayer Online Battle Arena (MOBA, "arena de batalha multijogador online"), também conhecido como Action Real-Time Strategy (ARTS, "estratégia de ação em tempo real") onde o jogador controla um personagem em uma batalha entre dois times, cujo objetivo é derrotar a equipe adversária e, para isso, é, indispensavelmente, necessário destruir o Ancestral (Ancient), que, por sua vez, se localiza no centro da base inimiga. Cada base é defendida por torres e ondas de unidades (chamadas de creeps) que percorrem os caminhos principais do mapa (chamadas de trilhas, ou lanes) que levam à suas bases. Existem 3 trilhas no mapa do jogo, que por convenção são chamadas de trilha de cima (Top, atravessa a parte de cima do mapa), trilha do meio (Mid ou Middle, caminho que atravessa o centro do mapa) e trilha de baixo (Bot ou Bottom, caminho que atravessa a parte de baixo do mapa).
Na imagem abaixo podemos ver o minimapa do Dota2 e ao lado um diagrama representando o layout de um jogo do gênero MOBA, onde os pontos roxos e rosas representam as torres de cada time, com exceção do maior ponto localizado nos cantos da imagem dentro de cada base, que representa o Ancient.
 |
|---|
| Figura 1: Minimapa do Dota 2 (esquerda) e layout padrão de jogos MOBA (direita) |
Dados¶
Os dados foram coletados utilizando a API não oficial do jogo, chamada OpenDoTA, nela coletamos dados de várias partidas do ano de 2015, assim como os dados dos jogadores presentes em cada partida e todas as suas ações, como por exemplo as coordenadas das posições que o jogador passou durante a partida, o herói que escolheu, quantas vezes morreu, entre outros. Os dados coletados foram disponibilizados em formato json, e não em formato de tabelas como precisávamos, então foi preciso selecionar os dados que queríamos entre aqueles fornecidos pela API e montar um banco de dados a partir deles.
Uma característica do formato json é usar o tipo abstrato de dados dicionário, que é composto de pares de chaves e valores, onde cada chave pode conter uma lista de outras chaves e seus valores, isso faz com que existam várias "sub-tabelas" nos dados, o que impossibilita a importação dos dados diretamente para um banco de dados convencional. Dessa forma, foram criadas tabelas separadas para cada conjunto de valores do json, sendo que cada tabela referencia o seu "pai" original através de chaves estrangeiras, dessa forma conseguimos manter a relação dos dados como era originalmente feito no json.
Um exemplo dos dados pode ser visto na imagem abaixo.
 |
|---|
| Figura 2: Amostra dos dados coletados em formato json |
Alguns dados não podiam ser buscados através da API, como por exemplo os nomes e números identificadores de cada personagem ou item do jogo, que são informações constantes, que não mudam entre as partidas, mas precisávamos dessas informações uma vez que a maior parte dos dados é numérica, e não seria interessante ter apenas números, mas sim nomes e significados para que os dados fizessem mais sentido. Por fim encontramos esses dados também em formato json, compatíveis com os dados da API, o que fez com que ficasse mais natural a relação entre as informações.
A imagem abaixo mostra um exemplo dos dados constantes de itens do jogo, com o id e nome de cada item listado em formato json.
 |
|---|
| Figura 3: Dados constantes do jogo, servem de referência para os dados das partidas |
Criação do banco de dados¶
Para criar o banco de dados baseado nas informações em json, primeiro escolhemos quais partes dos dados seriam utilizadas, uma vez que a quantidade de dados é muito extensa, e ficaria inviável modelar e inserir os dados em sua totalidade em um banco de dados devido à limitações de tempo. Depois disso foi decidido o que se tornaria tabela e o que se tornaria atributo. Seguimos um padrão no qual todas as chaves do json que possuíam uma lista de valores, se tornariam tabelas, e essas tabelas seriam relacionadas à tabela acima na hierarquia por meio de chaves estrangeiras. Usando como referência os dados da Figura 2, podemos ver que a parte referente aos dados da partida possui uma chave "players", que por sua vez possui várias informações pontuais e tambem possui chaves com listas e outras informações. Cada uma dessas chaves que possuem mais de um valor seriam candidatas para serem transformadas em tabelas, como pode ser visto no exemplo da imagem abaixo, onde a chave referente à partida (match) possui uma chave com vários valores chamada "players", nesse caso players se tornou uma tabela separada de match, relacionada à match por uma chave estrangeira.
 |
|---|
| Figura 4: Recorte do diagrama e dos dados para ilustrar o mapeamento do json para banco de dados relacional |
Por fim optamos por criar 20 tabelas, que contém desde informações das ações dos jogadores em cada partida, até o nome dos heróis e itens do jogo, passando claro pelas informações das partidas como time vencedor, duração em segundos, entre outros.
Diagrama entidade-relacionamento¶
Para a criação do diagrama entidade-relacionamento, utilizamos o software brModelo, que foi indicado pela professora em sala durante a discussão do trabalho final. O brModelo usa a notação do livro do Prof. Heuser, que é um pouco diferente da que foi vista em sala de aula, mas achamos bem intuitivo e conseguimos modelar o banco sem muitos problemas.
Abaixo podemos ver o diagrama criado para o nosso banco.
 |
|---|
| Figura 5: Diagrama entidade-relacionamento |
Esquema relacional¶
Para criar o esquema relacional, tivemos vários problemas com algumas ferramentas como o site erdplus.com, também indicado pela professora, e ao tentar utilizar ferramentas mais manuais como o site draw.io, vimos que levaria muito mais tempo do que nós tínhamos disponível. Por esse motivo, optamos por utilizar uma ferramenta mais profissional, no caso o MySQL Workbench, isso agilizou bastante o processo e nos ajudou a não cometer erros no diagrama, uma vez que essa é uma ferramenta especializada em banco de dados.
Para armazenar os dados, optamos por utilizar a biblioteca sqlite3 do python, que foi mostrada na aula ministrada pela nossa monitora, já que é mais prático manter os dados em um arquivo ao invés de utilizar um servidor MySQL. Uma limitação do MySQL Workbench era exportar o modelo apenas no padrão SQL do MySQL, e após alguns testes, vimos que esse padrão não era compatível com a sintaxe do SQLite. Para resolver esse problema, utilizamos um plugin do MySQL Workbench chamado ExportSQLite, que exporta o modelo relacional criado diretamente para código SQL compatível com SQLite.
Após feita a modelagem e criação do código em SQL para criação do banco, começamos a utilizar o Jupyter-Notebook para ler os dados dos arquivos json
 |
|---|
| Figura 6: Esquema relacional |
Referências¶
Dota 2 Wikipedia: https://en.wikipedia.org/wiki/Dota_2
MOBA: https://en.wikipedia.org/wiki/Multiplayer_online_battle_arena
OpenDoTA API: https://docs.opendota.com/
Descrição dos dados: https://wiki.teamfortress.com/wiki/WebAPI/GetMatchDetails
brModelo: http://www.sis4.com/brModelo/
MySQL Workbench: https://www.mysql.com/products/workbench/
Plugin do MySQL Workbench (exportar para SQLite): https://github.com/tatsushid/mysql-wb-exportsqlite
Código¶
import numpy as np
import pandas as pd
from matplotlib import pyplot as plt
import seaborn as sns
import io
import sqlite3
import json
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:80% !important; }</style>"))
Dados¶
with open('dota2_2800_partidas.json', 'r', encoding="utf8") as game_data:
json_data = json.load(game_data)
Criação do BD¶
conn = sqlite3.connect('SQL/dota2_2800_partidas.db')
cursor = conn.cursor()
f = io.open('SQL/Banco_Dota2.sql', 'r')
sql = f.read()
cursor.executescript(sql)
Inserção dos dados¶
#Dados do lobby_type.json
with open('json/lobby_type.json') as lobby_type_json:
lobby_type_data = json.load(lobby_type_json)
for l_id in lobby_type_data:
lobby_type_id = lobby_type_data[l_id]['id']
lobby_type_name = lobby_type_data[l_id]['name']
cursor.execute('''INSERT INTO lobby_type (id,name) VALUES(?,?)''', (lobby_type_id,lobby_type_name))
#Dados do game_mode.json
with open('json/game_mode.json') as game_mode_json:
game_mode_data = json.load(game_mode_json)
for g_id in game_mode_data:
game_mode_id = game_mode_data[g_id]['id']
game_mode_name = game_mode_data[g_id]['name']
cursor.execute('''INSERT INTO game_mode (id,name) VALUES(?,?)''', (game_mode_id,game_mode_name))
#Dados do order_types.json
with open('json/order_types.json') as order_types_json:
order_types_data = json.load(order_types_json)
for o_id in order_types_data:
order_type_id = o_id
order_type_name = order_types_data[o_id]
cursor.execute('''INSERT INTO order_types (id,name) VALUES(?,?)''', (order_type_id,order_type_name))
#Dados do gold_reasons.json
with open('json/gold_reasons.json') as gold_reasons_json:
gold_reasons_data = json.load(gold_reasons_json)
for gold_id in gold_reasons_data:
gold_reasons_id = gold_id
gold_reasons_name = gold_reasons_data[gold_id]['name']
cursor.execute('''INSERT INTO gold_reasons (id,name) VALUES(?,?)''', (gold_reasons_id,gold_reasons_name))
#Dados do xp_reasons.json
with open('json/xp_reasons.json') as xp_reasons_json:
xp_reasons_data = json.load(xp_reasons_json)
for x_id in xp_reasons_data:
xp_reasons_id = x_id
xp_reasons_name = xp_reasons_data[x_id]
cursor.execute('''INSERT INTO xp_reasons (id,name) VALUES(?,?)''', (xp_reasons_id,xp_reasons_name))
#Dados do item_ids.json
with open('json/item_ids.json') as item_ids_json:
item_ids_data = json.load(item_ids_json)
for i_id in item_ids_data:
item_ids_id = i_id
item_ids_name = item_ids_data[i_id]
cursor.execute('''INSERT INTO item_ids (id,name) VALUES(?,?)''', (item_ids_id,item_ids_name))
#Dados do heroes.json
with open('json/heroes.json') as heroes_json:
heroes_data = json.load(heroes_json)
for h_id in heroes_data:
hero_id = heroes_data[h_id]['id']
hero_name = heroes_data[h_id]['name']
hero_localized_name = heroes_data[h_id]['localized_name']
primary_attr = heroes_data[h_id]['primary_attr']
attack_type = heroes_data[h_id]['attack_type']
cursor.execute('''INSERT INTO heroes (hero_id,name,localized_name,primary_attr,attack_type)
VALUES(?,?,?,?,?)''', (hero_id,hero_name,hero_localized_name,primary_attr,attack_type))
conn.commit()
#match
for match in json_data:
match_id = match['match_id']
radiant_win = match['radiant_win']
duration = match['duration']
tower_status_dire = match['tower_status_dire']
tower_status_radiant = match['tower_status_radiant']
barracks_status_radiant = match['barracks_status_radiant']
barracks_status_dire = match['barracks_status_dire']
first_blood_time = match['first_blood_time']
lobby_type = match['lobby_type']
human_players = match['human_players']
game_mode = match['game_mode']
cursor.execute('''INSERT INTO match (match_id,game_mode, lobby_type, radiant_win, duration,
human_players, tower_status_dire, tower_status_radiant, barracks_status_radiant,
barracks_status_dire, first_blood_time) VALUES(?,?,?,?,?,?,?,?,?,?,?)''',
(match_id,game_mode, lobby_type, radiant_win, duration, human_players, tower_status_dire,
tower_status_radiant, barracks_status_radiant, barracks_status_dire, first_blood_time))
#chat
for chat_message in match['chat']:
chat_match_id = match_id
time = chat_message['time']
type_ = chat_message['type']
unit = chat_message['unit']
key = chat_message['key']
slot = chat_message['slot']
cursor.execute('''INSERT INTO chat (chat_match_id,ROWID,time,type,unit,key,slot) VALUES(?,?,?,?,?,?,?)''',
(chat_match_id,None,time,type_,unit,key,slot))
for player in match['players']:
player_match_id = match_id
player_slot = player['player_slot']
level = player['level']
hero_healing = player['hero_healing']
leaver_status = player['leaver_status']
last_hits = player['last_hits']
denies = player['denies']
gold_per_min = player['gold_per_min']
gold_spent = player['gold_spent']
gold = player['gold']
xp_per_min = player['xp_per_min']
hero_id = player['hero_id']
hero_damage = player['hero_damage']
tower_damage = player['tower_damage']
kills = player['kills']
deaths = player['deaths']
assists = player['assists']
item_0 = player['item_0']
item_1 = player['item_1']
item_2 = player['item_2']
item_3 = player['item_3']
item_4 = player['item_4']
item_5 = player['item_5']
# Ignorar os Bots (tem account_id = None)
account_id = player['account_id']
if account_id is None: continue
cursor.execute('''INSERT INTO player (player_match_id,gold_spent,gold,xp_per_min,level,hero_id,
hero_healing,hero_damage,leaver_status,tower_damage,last_hits,kills,denies,deaths,gold_per_min,
item_0,item_1,item_2,item_3,item_4,item_5,assists,player_slot)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
(player_match_id,gold_spent,gold,xp_per_min,level,hero_id,
hero_healing,hero_damage,leaver_status,tower_damage,last_hits,kills,denies,deaths,gold_per_min,
item_0,item_1,item_2,item_3,item_4,item_5,assists,player_slot))
for action in player['actions']:
action_match_id = match_id
action_player_slot = player_slot
action_id = action
action_count = player['actions'][action]
cursor.execute('''INSERT INTO actions (action_match_id,action_player_slot,action_id,action_count)
VALUES(?,?,?,?)''', (action_match_id,action_player_slot,action_id,action_count))
for x_coord in player['lane_pos']:
for y_coord in player['lane_pos'][x_coord]:
pos_match_id = match_id
pos_player_slot = player_slot
x_pos = x_coord
y_pos = y_coord
pos_count = player['lane_pos'][x_coord][y_coord]
cursor.execute('''INSERT INTO lane_pos (pos_match_id,pos_player_slot,x_pos,y_pos,pos_count)
VALUES(?,?,?,?,?)''', (pos_match_id,pos_player_slot,x_pos,y_pos,pos_count))
for x_coord in player['obs']:
for y_coord in player['obs'][x_coord]:
obs_match_id = match_id
obs_player_slot = player_slot
x_pos = x_coord
y_pos = y_coord
obs_count = player['obs'][x_coord][y_coord]
cursor.execute('''INSERT INTO obs (obs_match_id,obs_player_slot,x_pos,y_pos,obs_count)
VALUES(?,?,?,?,?)''', (obs_match_id,obs_player_slot,x_pos,y_pos,obs_count))
for x_coord in player['sen']:
for y_coord in player['sen'][x_coord]:
sen_match_id = match_id
sen_player_slot = player_slot
x_pos = x_coord
y_pos = y_coord
sen_count = player['sen'][x_coord][y_coord]
cursor.execute('''INSERT INTO sen (sen_match_id,sen_player_slot,x_pos,y_pos,sen_count)
VALUES(?,?,?,?,?)''', (sen_match_id,sen_player_slot,x_pos,y_pos,sen_count))
for player_gold_reasons in player['gold_reasons']:
gold_match_id = match_id
gold_player_slot = player_slot
gold_id = player_gold_reasons
gold_count = player['gold_reasons'][gold_id]
cursor.execute('''INSERT INTO player_gold_reasons (gold_match_id,gold_player_slot,
gold_id,gold_count)
VALUES(?,?,?,?)''', (gold_match_id,gold_player_slot,gold_id,gold_count))
for player_xp_reasons in player['xp_reasons']:
xp_match_id = match_id
xp_player_slot = player_slot
xp_id = player_xp_reasons
xp_count = player['xp_reasons'][xp_id]
cursor.execute('''INSERT INTO player_xp_reasons (xp_match_id,xp_player_slot,
xp_id,xp_count)
VALUES(?,?,?,?)''', (xp_match_id,xp_player_slot,xp_id,xp_count))
for player_damage in player['damage']:
damage_match_id = match_id
damage_player_slot = player_slot
unit_damaged = player_damage
damage_count = player['damage'][unit_damaged]
cursor.execute('''INSERT INTO damage (damage_match_id,damage_player_slot,unit_damaged,damage_count)
VALUES(?,?,?,?)''', (damage_match_id,damage_player_slot,unit_damaged,damage_count))
for player_healing in player['healing']:
healing_match_id = match_id
healing_player_slot = player_slot
unit_healed = player_healing
healing_count = player['healing'][unit_healed]
cursor.execute('''INSERT INTO healing (healing_match_id,healing_player_slot,unit_healed,healing_count)
VALUES(?,?,?,?)''', (healing_match_id,healing_player_slot,unit_healed,healing_count))
for player_killed in player['killed']:
killed_match_id = match_id
killed_player_slot = player_slot
unit_killed = player_killed
killed_count = player['killed'][unit_killed]
cursor.execute('''INSERT INTO killed (killed_match_id,killed_player_slot,unit_killed,killed_count)
VALUES(?,?,?,?)''', (killed_match_id,killed_player_slot,unit_killed,killed_count))
for item_uses in player['item_uses']:
item_match_id = match_id
item_player_slot = player_slot
item_name = item_uses
item_count = player['item_uses'][item_name]
cursor.execute('''INSERT INTO item_uses (item_match_id,item_player_slot,item_name,item_count)
VALUES(?,?,?,?)''', (item_match_id,item_player_slot,item_name,item_count))
for kill in player['kills_log']:
kills_log_match_id = match_id
kills_log_player_slot = player_slot
time = kill['time']
key = kill['key']
cursor.execute('''INSERT INTO kills_log (kills_log_match_id,kills_log_player_slot,ROWID,time,key)
VALUES(?,?,?,?,?)''', (kills_log_match_id,kills_log_player_slot,None,time,key))
conn.commit()
Consultas realizadas¶
Sabemos que as tabelas principais são 'match' e 'player', vamos ver como os dados nessas tabelas estão distribuídos.
Primeiro vamos analisar a tabela das partidas.
df_match = pd.read_sql_query('''SELECT * FROM match''', conn)
print('Tamanho da tabela match:', df_match.shape)
df_match.describe()
Podemos ver que temos 2856 partidas no banco, então podemos esperar cerca de 10 vezes esse número na tabela de players, já que em geral são 10 por partida. Podemos notar também que o time Radiant ganha em pouco mais da metade das partidas. Outro dado importante é que existem partidas com bots na base, uma vez que a coluna 'human_players' não tem média igual a 10.
Agora vamos analisar os dados da tabela de jogadores.
df_player = pd.read_sql_query('''SELECT * FROM player''', conn)
pd.set_option('display.max_columns', 23)
print('Tamanho da tabela player:', df_player.shape)
df_player.describe()
Como esperado, temos cerca que 10 jogadores para cada partida (não exatamente 10 por causa da existência de partidas com bots que não contam como jogadores na nossa base). Alguns dados importantes que conseguimos ver são, a média de 'kills', 'deaths' e 'assists' que compoem uma estatística chamada KDA, muito usada no jogo. A quantidade de gold e experiência por minuto também é um dado interessante, que pode interferir no resultado da partida. Também podemos usar a quantidade de last_hits e kills para ver quais personagens tem o maior número de vítimas em média por exemplo.
Seleção e projeção¶
Consulta: Quantidade de mensagens ao longo da partida¶
Nesta consulta ao banco de dados, verificamos o tempo em que os jogadores acessam o chat durante a partida para se comunicarem. Para isso, consultamos o atributo "time" da tabela "chat" de todas as partidas e plotamos na forma de um histograma, e como a duração das partidas varia, plotamos a duração média das partidas para efeito de referência, consultando o atributo "duration" da tabela "match", e fazendo a média dos valores da duração de todas as partidas.
df_chat = pd.read_sql_query(
'''
SELECT time
FROM chat
''', conn)
df_match_duration = pd.read_sql_query(
'''
SELECT AVG(duration)
FROM match
''', conn)
df_match_duration = df_match_duration.iloc[0][0]
display(df_chat.head())
plt.figure(figsize=(16,5))
plt.hist(df_chat['time'], bins=200)
plt.vlines(df_match_duration, 0, 1220, color='red')
plt.text(df_match_duration+50, 1200, 'Duração média das partidas')
plt.title("Quantidade de mensagens de texto ao longo das partidas", fontsize=20)
plt.show()
Consulta: Posição das wards colocadas pelos jogadores¶
Para esta consulta, selecionamos as coordenadas "x" e "y" do mapa em que as wards observadoras foram colocadas pelos jogadores em todas as partidas do nosso banco de dados, agrupamos as posições repetidas e somamos a quantidade de vezes que a ward foi posicionada nessas posições. Para isso realizamos uma seleção das posições e um "sum" das posições repetidas da tabela "obs" e demos um "group by" na posição "x" e "y". A partir dos dados gerados, plotamos um mapa de calor das posições das wards e, no fundo do mapa de calor colocamos a imagem do mapa do jogo, sendo possível assim visualizar as posições de wards mais utilizadas pelos jogadores nas partidas. Essa análise pode ser muito útil e determinante em uma partida pois comprando-se as sentry wards, que são as wards responsáveis por revelar as wards observadoras do time inimigo, e posicionando-as nesses locais, a chance de encontrar as wards inimigas é maior, o que possibilita detrui-las, e assim reduzir a "visão" do mapa pelo time inimigo.
df_obs = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(obs_count) count
FROM obs
GROUP BY x_pos, y_pos
''', conn)
def dota2_heatmap(df, df_img, sizex, sizey, bins, name, title_height):
img = plt.imread("Minimap_6.88.jpg")
fig, ax = plt.subplots(figsize=(sizex,sizey))
fig.suptitle(name, fontsize=28, y=title_height)
ax.imshow(img, extent=[df_img['x'].min(), df_img['x'].max(), df_img['y'].min(), df_img['y'].max()])
heatmap, xedges, yedges = np.histogram2d(df['x'], df['y'], bins=bins)
extent = [xedges[0], xedges[-1], yedges[0], yedges[-1]]
ax.imshow(heatmap.T, extent=extent, origin='lower', alpha=0.5, interpolation='bicubic')
dota2_heatmap(df_obs, df_obs, 15, 15, 35, 'Heatmap - Observer Wards', 0.9)
df_obs.head()
Consulta: Posição nas lanes de acordo com cada time em uma partida específica¶
Para esta consulta fizemos duas seleções, uma para o time "Radiant" e outro para o "Dire", com o intuito de observar o posicionamento dos heróis em cada uma das equipes em uma partida de longa duração, específica da base de dados. Selecionamos as posições "x" e "y" visitadas por cada herói na partida e somamos as posições repetidas guardando-as em uma coluna separada, para assim mapearmos as posições mais visitadas pelos jogadores. Para identificarmos de qual time cada jogador pertence, utilizamos a informação do atributo "pos_player_slot", que identica de 0 a 4 os jogadores do time "Radiant" e de 128 a 132 os jogadores do time "Scourge". Para melhor visualização das posições mais visitadas pelos jogadores de cada time, plotamos dois mapas de calor com os dados, um para cada time.
df_lane_radiant = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(pos_count) count
FROM lane_pos
WHERE pos_player_slot < 5 AND pos_match_id = 2001310590
GROUP BY x_pos, y_pos
''', conn)
dota2_heatmap(df_lane_radiant, df_obs, 15, 15, 35, 'Heatmap - Radiant Lane Position', 0.84)
df_lane_dire = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(pos_count) count
FROM lane_pos
WHERE pos_player_slot > 127 AND pos_match_id = 2001310590
GROUP BY x_pos, y_pos
''', conn)
dota2_heatmap(df_lane_dire, df_obs, 15, 15, 35, 'Heatmap - Dire Lane Position', 0.86)
Junção de duas relações¶
Consulta: Estatísticas relacionadas ao resultado das partidas¶
Essa consulta mostra a média de várias estatísticas dos jogadores, de acordo com o resultado das partidas, tanto para a vitória quanto para a derrota, com o objetivo de visualizar e comparar características e estratégias individuais e coletivas que influenciam um time à vitória ou à derrota.
df_radiant = pd.read_sql_query(
'''
SELECT REPLACE(REPLACE(radiant_win,0,"Derrota"),1,"Vitória") Resultado , (AVG(kills) - AVG(deaths)) "Saldo de Vítimas",
AVG(hero_healing), AVG(tower_damage), AVG(hero_damage), AVG(gold_per_min), AVG(gold_spent), AVG(xp_per_min), AVG(denies), AVG(last_hits), AVG(level)
FROM match AS M
INNER JOIN(
SELECT player_match_id, player_slot, kills, deaths, hero_healing, tower_damage, hero_damage, gold_per_min, xp_per_min, denies, last_hits, level, gold_spent
FROM player
WHERE player_slot < 5
) AS P
ON M.match_id = P.player_match_id
GROUP BY Resultado
ORDER BY Resultado DESC
''', conn)
df_radiant
Para ajudar a visualizar melhor os dados, podemos condensar algumas tabelas importantes em um único dataframe e gerar histogramas comparando os resultados das partidas.
Sabemos que em cada partida, existem 10 jogadores, 5 em cada um dos dois times, já que vamos ignorar as partidas com bots, então, uma maneira de organizar os dados é somando as estatísticas de cada time e adicionar essas estatísticas nas linhas de cada partida, separando as partidas por time, ou seja, onde antes existia uma entrada por partida, agora irá existir uma entrada por time para cada uma das partidas. Também adicionamos os nomes dos modos de jogo e tipo de lobby (casual, rankeado, etc).
df = pd.read_sql_query('''
SELECT "match_id" "Match ID", "game_mode_name" "Modo de jogo", "lobby_type_name" "Modo de lobby", "duration" "Duração", REPLACE(REPLACE("radiant_win",1,"Radiant"),0,"Dire") "Winner", "Team", "AVG(gold_spent)" "Ouro gasto em média",
"AVG(gold)" "Média de ouro sobrando ao final da partida", "AVG(xp_per_min)" "Média de experiência/min", "AVG(level)" "Média de nível dos personagens", "AVG(hero_healing)" "Média de cura",
"AVG(hero_damage)" "Média de dano nos heróis inimigos", "AVG(tower_damage)" "Média de dano nas torres inimigas", "AVG(last_hits)" "Last hits nas creeps", "sum(kills)" "Vítimas",
"sum(denies)" "Denies", "sum(deaths)" "Mortes", "AVG(gold_per_min)" "Média de ouro/min", "sum(assists)" "Assists"
, "tower_status_dire",
"tower_status_radiant", "barracks_status_radiant",
"barracks_status_dire", "first_blood_time"
FROM match AS m
INNER JOIN
(SELECT player_match_id,REPLACE(REPLACE(sum(player_slot),10,"Radiant"),650,"Dire") Team,AVG(gold_spent),AVG(gold),AVG(xp_per_min),AVG(level),AVG(hero_healing),AVG(hero_damage),AVG(tower_damage),AVG(last_hits),sum(kills),sum(denies),sum(deaths),AVG(gold_per_min),sum(assists)
FROM player
WHERE player_slot < 5
GROUP BY player_match_id
UNION
SELECT player_match_id,REPLACE(REPLACE(sum(player_slot),10,"Radiant"),650,"Dire") Team,AVG(gold_spent),AVG(gold),AVG(xp_per_min),AVG(level),AVG(hero_healing),AVG(hero_damage),AVG(tower_damage),AVG(last_hits),sum(kills),sum(denies),sum(deaths),AVG(gold_per_min),sum(assists)
FROM player
WHERE player_slot > 5
GROUP BY player_match_id) AS p
ON m.match_id = p.player_match_id
INNER JOIN
(SELECT id game_mode_id, name game_mode_name
FROM game_mode) as gm
ON m.game_mode = gm.game_mode_id
INNER JOIN
(SELECT id lobby_type_id, name lobby_type_name
FROM lobby_type) as lt
ON m.lobby_type = lt.lobby_type_id
WHERE human_players = 10
''', conn)
pd.set_option('display.max_columns', 50)
df.head(10)
Vamos visualizar como é a proporção de vitórias por time
colors = ["#d66853", "#0081af"]
plt.figure(figsize=(10,5))
sns.countplot('Winner', data=df, palette=colors)
plt.title('Distribuição das vitórias por time', fontsize=16)
plt.xlabel('Resultado', fontsize=15)
plt.ylabel('Quantidade de vitórias', fontsize=15)
Ainda utilizando a consulta anterior, podemos visualizar a diferença entre as estatísticas das vitórias e derrotas.
# Dataframe contendo apenas vitórias
df_wins = df[((df.Winner == 'Radiant') & (df.Team == 'Radiant')) | ((df.Winner == 'Dire') & (df.Team == 'Dire'))]
# Dataframe contendo apenas derrotas
df_losses = df[((df.Winner == 'Dire') & (df.Team == 'Radiant')) | ((df.Winner == 'Radiant') & (df.Team == 'Dire'))]
wins = df_wins.drop(['Match ID', 'Duração', 'tower_status_dire', 'tower_status_radiant', 'barracks_status_radiant', 'barracks_status_dire', 'first_blood_time'], axis=1)
losses = df_losses.drop(['Match ID', 'Duração', 'tower_status_dire', 'tower_status_radiant', 'barracks_status_radiant', 'barracks_status_dire', 'first_blood_time'], axis=1)
# fig, ax = plt.subplots(nrows=wins.shape[0], ncols=wins.shape[1])
i = 0
plt.figure(figsize=(22,15))
for col in wins.columns:
if type(wins[col].iat[0]).__name__ == 'str':
continue
i+=1
plt.subplot(4,4,i)
plt.hist(wins[col], bins=18, color='#0081af', alpha=0.8, label="Vitória")
plt.hist(losses[col], bins=18, color='#d66853', alpha=0.8, label="Derrota")
plt.title(col)
plt.legend()
plt.show
Consulta: Quais são os itens mais usados no jogo?¶
Esta consulta verifica os itens utilizados pelos jogadores em todas as partidas com seus respectivos nomes, e soma a quantidade dos itens comprados em todas as partidas agrupando pelo nome de cada item, com o objetivo de verificar quais são os itens utilizáveis mais populares entre os jogadores.
df_mais_comprado = pd.read_sql_query(
'''
SELECT name, sum(item_count) total
FROM item_uses AS USES
INNER JOIN (
SELECT name
FROM item_ids
) AS IDS
ON USES.item_name = IDS.name
GROUP BY USES.item_name
ORDER BY total DESC
''', conn)
df_mais_comprado.head(10)
Consulta: Ordem das ações mais executadas no jogo¶
Esta consulta busca ordenar os comandos mais executados pelos jogadores em todas as partidas, representado em ordem decrescente. Para isso, coletamos os comandos executados por cada jogador individualmente e realizamos uma contagem em cima dos dados, agrupando por nome do comando. Foi concluído que o comando mais executado é o de andar, que é quando o jogador clica em uma posição no chão e o seu herói anda até lá.
df_actions = pd.read_sql_query(
'''
SELECT name, sum(action_count) action_count
FROM actions AS A
INNER JOIN(
SELECT name, id
FROM order_types
) AS O
ON A.action_id = O.id
GROUP BY name
ORDER BY action_count DESC
''', conn)
df_actions.head(10)
Junção de três ou mais relações¶
Consulta: Qual o herói com maior porcentagem de vitórias?¶
O objetivo desta consulta é verificar os heróis com maior taxa de vitória em todas partidas, utilizando o nome como fator de agrupamento dos dados. Para evitar heróis que estiveram pouco presente nas partidas, decidimos estabelecer que só entrariam heróis com mais de 100 partidas na base de dados, assim evitando dados enviesados, por exemplo, se um herói só foi escolhido uma vez e ganhou, sua taxa de vitória seria de 100%, mas isso não necessariamente condiz com a realidade. Para conseguirmos realizar esta consulta tivemos de realizar junção de três relações, tabelas "match", "player" e "heroes", e para calcular a taxa de vitória fizemos a soma do número de partidas vencedoras do herói dividido pelo número de partidas em que o herói foi selecionado.
df_winner_heroes = pd.read_sql_query(
'''
SELECT localized_name 'Hero Name', CAST(count(radiant_win) AS float)/CAST(M.num_matches AS float) WinRate
FROM match
INNER JOIN(
SELECT player_match_id, localized_name, num_matches
FROM player P
INNER JOIN(
SELECT hero_id, localized_name, num_matches
FROM heroes
INNER JOIN(
SELECT hero_id n_id, count(hero_id) num_matches
FROM player
GROUP BY hero_id
) AS NUMP
ON heroes.hero_id = NUMP.n_id
) AS H
ON P.hero_id = H.hero_id
WHERE player_slot < 5
) AS M
ON match_id = player_match_id
WHERE M.num_matches > 100
GROUP BY localized_name
ORDER BY WinRate DESC
''', conn)
df_winner_heroes.head(20)
Consulta: Herói mais escolhido em partidas rankeadas¶
As partidas rankeadas possuem como característica um ambiente mais competitivo entre os times, pois o resultado da partida gera pontos que classificam cada jogador individualmente em um ranking de habilidades, onde o time ganhador e os jogadores que mais se sobressaíram podem subir de nível e ser melhor classificados de acordo com suas habilidades. Nesses tipos de partidas, as estratégias de equipe são mais bem pensadas e os jogadores escolhem heróis que julgam ter melhores performances individuais ou em equipe, consequentemente, o nível de dificuldade da partida aumenta. Diante deste contexto, esta consulta busca classificar a popularidade de escolha dos heróis para partidas rankeadas, do mais para o menos popular, e soma suas aparições nas partidas para melhor visualização.
df_most_picked_ranked = pd.read_sql_query(
'''
SELECT mode_name Modo, HR.hero_name Herói, count(HR.hero_name) Aparições
FROM match AS M
INNER JOIN(
SELECT id, name mode_name
FROM lobby_type
WHERE name = 'lobby_type_ranked'
) AS L
ON M.lobby_type = L.id
INNER JOIN(
SELECT player_match_id, hero_id, hero_name
FROM player AS P
INNER JOIN(
SELECT hero_id id, localized_name hero_name
FROM heroes
)AS H
ON P.hero_id = H.id
)as HR
ON M.match_id = HR.player_match_id
GROUP BY HR.hero_name
ORDER BY Aparições DESC
''', conn)
df_most_picked_ranked.head()
Consulta: Last hits e Denies por Herói¶
Para os heróis conseguirem acumular dinheiro é necessário dar o golpe derradeiro em criaturas inimigas, criaturas neutras do mapa ou em heróis inimigos, esse golpe é chamado de "last hit". A forma de evitar que o herói inimigo dê o last hit em criaturas do seu time é o "deny", golpe derradeiro desferido pelo herói à criatura do próprio time, o que só pode ser feito se a criatura estiver com pouquíssima vida. No início do jogo, conseguir dar o last hit nas criaturas inimigas é fundamental para acumular dinheiro mais rapidamente e, por consequência, ter acesso a itens mais caros antes da equipe inimiga, o que pode garantir vantagem em batalhas.
Nesta consulta selecionamos os heróis e suas respectivas médias de last hits e denies por partida a partir do número total de partidas da nossa base de dados, e ordenamos de forma decrescente a média de last hits dos heróis identificados aqui pelo nome, para demonstrar aqueles que possuem melhor desempenho na média.
df = pd.read_sql_query(
'''
SELECT localized_name "Hero Name", sum(last_hits)/count(localized_name) "Total Last Hits", sum(denies)/count(localized_name) "Total Denies"
FROM match AS M
INNER JOIN(
SELECT id, name mode_name
FROM lobby_type
WHERE name = 'lobby_type_ranked'
) AS L
ON M.lobby_type = L.id
INNER JOIN(
SELECT player_match_id, localized_name, last_hits, denies
FROM player P
INNER JOIN(
SELECT hero_id id ,localized_name
FROM heroes
) AS H
ON P.hero_id = H.id
) AS M
ON match_id = player_match_id
GROUP BY "Hero Name"
ORDER BY "Total Last Hits" DESC
''', conn)
df.head(10)
Consulta: Proporção de Vítimas e Assistencias por Mortes para cada Herói. Qual o herói com maior KDA?¶
Uma forma de analisar o desempenho dos heróis na partida é analisar o seu KDA (kill, deaths, assists), quanto maior o seu KDA, melhor desempenho o herói teve na partida. Para se calcular esse índice devemos seguir essa fórmula:
KDA = $(kills + assists)/MAX(1,deaths).$
Nesta consulta, calculamos o KDA de cada herói e fizemos a média em todas as partidas da base, e para melhor visualização dos melhores heróis de acordo com esse índice, ordenamos de forma descrescente a lista com todos os heróis do banco.
df_kda = pd.read_sql_query(
'''
SELECT hero_name 'Hero Name', sum(kills) Kills, sum(assists) Assists, sum(deaths) Deaths, (CAST(sum(kills) AS float)+CAST(sum(assists) AS float))/CAST(sum(deaths) AS float) "KDA Ratio"
FROM player AS P
INNER JOIN(
SELECT hero_id id, localized_name hero_name
FROM heroes
) AS H
ON P.hero_id = H.id
GROUP BY hero_id
ORDER BY "KDA Ratio" DESC
''', conn)
df_kda.head(10)
Consulta: Dano médio em torres de cada herói¶
O objetivo do jogo é destruir a torre "ancient" do time inimigo, para isso, é necessário que, no mínimo, todas as torres inimigas de uma das lanes (bot, mid, top) do mapa sejam completamente destruídas.
Então um fator que pode ser determinante para se vencer a partida é possuir heróis que desfiram golpes e magias que causem grande quantidade de dano nas torres inimigas.
Nesta consulta buscamos identificar os heróis que causam mais dano em média nas torres inimigas, e para enriquecer as características da consulta e dos heróis, selecionamos o atributo primário do herói (força, agilidade ou inteligência) e também sua forma de ataque (melee ou ranged).
df_kda = pd.read_sql_query(
'''
SELECT hero_name "Hero Name", primary_attr "Primary Attribute", attack_type "Attack Type", AVG(tower_damage) "Average Tower Damage per Match"
FROM player AS P
INNER JOIN(
SELECT hero_id id, localized_name hero_name, primary_attr, attack_type
FROM heroes
)AS H
ON P.hero_id = H.id
GROUP BY hero_id
ORDER BY "Average Tower Damage per Match" DESC
''', conn)
df_kda.head(10)